import datetime
import json

import cx_Oracle

"""查询物料系统推送订单在入库单查不到的问题"""

user = "pigpos"
passwd = "P1g#2023pos"
# listener = 'CPGCNXL.cpchina.cn/CPGCNXL'
# listener = '10.240.24.135/NMDCFARM'
listener = 'CTCNZQF.cpchina.cn/CTCNZQF'
conn = cx_Oracle.connect(user, passwd, listener)
cursor = conn.cursor()
print_sql_flag = 0


def select(sql) -> json:
    if print_sql_flag == 1:
        print(sql)
    rows = []
    cursor.execute(sql)
    result = cursor.fetchall()
    col_name = cursor.description
    for row in result:
        d = {}
        for col in range(len(col_name)):
            key = col_name[col][0]
            value = row[col]
            if isinstance(value, datetime.datetime):
                value = value.strftime('%Y-%m-%d %H:%M:%S')
            d[key] = value
        rows.append(d)
    js = json.dumps(rows, sort_keys=True, ensure_ascii=False, separators=(',', ':'))
    return js


# 关闭连接，释放资源
def disconnect():
    cursor.close()
    conn.close()
    print("关闭数据库连接")


if __name__ == '__main__':
    print('开始查询')
    sql = "select * from ZCN_SW_SALE_EXPAND t where t.org_code in ( select org_code from mas_org mo where mo.company_code = '0648' ) and t.create_date >= to_date('2025/09/01','yyyy/mm/dd') and (t.business_type2 is null or t.business_type1 is null) order by t.create_date desc "
    rs = select(sql)
    rs = json.loads(rs)
    for d in rs:
        org_code = d['ORG_CODE']
        # print(org_code)
        document_no = d['DOCUMENT_NO']
        print(
            f"update ZCN_SW_SALE_EXPAND set business_type1 = 'C01' ,business_type2 =  'C0103'  where org_code = '{org_code}' and document_no = '{document_no}';")
